import { Database, defaultMysqlDatabase } from './db.mjs'
import { DeviceConfigMessage } from '../types/device-config.mjs'

export type AuthCodeStatus = 'PENDING' | 'VERIFIED' | 'EXPIRED'
export type DeviceConfigUpdatedBy = 'USER' | 'DEVICE' | 'SYSTEM'

export interface UserRecord {
  id: number
  phone: string
  createdAt: Date
  updatedAt: Date
}

export interface AuthCodeRecord {
  id: number
  phone: string
  code: string
  status: AuthCodeStatus
  expireAt: Date
  createdAt: Date
  updatedAt: Date
}

export interface DeviceRecord {
  id: number
  deviceId: string
  name: string | null
  hardwareInfo: Record<string, any> | null
  lastStatus: Record<string, any> | null
  lastSeenAt: Date | null
  createdAt: Date
  updatedAt: Date
}

export interface UserDeviceBindingRecord {
  id: number
  userId: number
  deviceId: number
  status: 'BOUND' | 'UNBOUND'
  boundAt: Date | null
  unboundAt: Date | null
  createdAt: Date
  updatedAt: Date
}

export interface DeviceConfigRecord {
  id: number
  deviceId: number
  configs: DeviceConfigMessage[]
  updatedBy: DeviceConfigUpdatedBy
  createdAt: Date
  updatedAt: Date
}

export interface Storage {
  createAuthCode(phone: string, code: string, expireAt: Date): Promise<void>
  findValidAuthCode(phone: string, code: string): Promise<AuthCodeRecord | undefined>
  markAuthCodeStatus(id: number, status: AuthCodeStatus): Promise<void>

  findUserByPhone(phone: string): Promise<UserRecord | undefined>
  findOrCreateUserByPhone(phone: string): Promise<UserRecord>
  createUserToken(userId: number, token: string): Promise<void>
  getUserByToken(token: string): Promise<UserRecord | undefined>

  findDeviceByDeviceIdentifier(deviceIdentifier: string): Promise<DeviceRecord | undefined>
  ensureDevice(deviceIdentifier: string, name?: string | null, hardwareInfo?: Record<string, any> | null): Promise<DeviceRecord>
  markDeviceOnline(deviceIdentifier: string): Promise<void>
  markDeviceOffline(deviceIdentifier: string): Promise<void>

  listBoundDevicesForUser(userId: number): Promise<DeviceRecord[]>
  getBoundDeviceForUser(userId: number, deviceIdentifier: string): Promise<DeviceRecord | undefined>
  bindUserToDevice(userId: number, device: DeviceRecord): Promise<UserDeviceBindingRecord>

  upsertDeviceConfig(device: DeviceRecord, configs: DeviceConfigMessage[], updatedBy: DeviceConfigUpdatedBy): Promise<DeviceConfigRecord>
  getDeviceConfig(device: DeviceRecord): Promise<DeviceConfigRecord | undefined>

  updateDeviceLastStatus(deviceIdentifier: string, status: Record<string, any> | null): Promise<void>
}

class MysqlStorage implements Storage {
  constructor(private db: Database) { }

  async createAuthCode(phone: string, code: string, expireAt: Date): Promise<void> {
    await this.db.sql(
      `insert into auth_codes (phone, code, status, expire_at) values (?, ?, 'PENDING', ?)`,
      [phone, code, expireAt]
    )
  }

  async findValidAuthCode(phone: string, code: string): Promise<AuthCodeRecord | undefined> {
    const result = await this.db.sql(
      `select * from auth_codes where phone = ? and code = ? and status = 'PENDING' and expire_at >= ? order by id desc limit 1`,
      [phone, code, new Date()]
    ) as Record<string, any>[]
    return toCamelCase<AuthCodeRecord>(result[0])
  }

  async markAuthCodeStatus(id: number, status: AuthCodeStatus): Promise<void> {
    await this.db.sql(`update auth_codes set status = ? where id = ?`, [status, id])
  }

  async findUserByPhone(phone: string): Promise<UserRecord | undefined> {
    const result = await this.db.sql(`select * from users where phone = ? limit 1`, [phone]) as Record<string, any>[]
    return toCamelCase<UserRecord>(result[0])
  }

  async findOrCreateUserByPhone(phone: string): Promise<UserRecord> {
    const existing = await this.findUserByPhone(phone)
    if (existing) {
      return existing
    }

    await this.db.sql(`insert into users (phone) values (?)`, [phone])
    const created = await this.findUserByPhone(phone)
    if (!created) {
      throw new Error('Failed to create user')
    }
    return created
  }

  async createUserToken(userId: number, token: string): Promise<void> {
    await this.db.sql(`insert into user_tokens (user_id, token, revoked) values (?, ?, 0)`, [userId, token])
  }

  async getUserByToken(token: string): Promise<UserRecord | undefined> {
    const result = await this.db.sql(
      `select u.* from user_tokens t join users u on t.user_id = u.id where t.token = ? and t.revoked = 0 limit 1`,
      [token]
    ) as Record<string, any>[]
    return toCamelCase<UserRecord>(result[0])
  }

  async findDeviceByDeviceIdentifier(deviceIdentifier: string): Promise<DeviceRecord | undefined> {
    const result = await this.db.sql(`select * from devices where device_id = ? limit 1`, [deviceIdentifier]) as Record<string, any>[]
    const record = toCamelCase<DeviceRecord>(result[0])
    return record ? normalizeDeviceRecord(record) : undefined
  }

  async ensureDevice(deviceIdentifier: string, name?: string | null, hardwareInfo?: Record<string, any> | null): Promise<DeviceRecord> {
    const existing = await this.findDeviceByDeviceIdentifier(deviceIdentifier)
    if (!existing) {
      await this.db.sql(
        `insert into devices (device_id, name, hardware_info, last_status) values (?, ?, ?, ?)`,
        [deviceIdentifier, name ?? null, stringifyJSON(hardwareInfo), stringifyJSON({ online: false })]
      )
      const created = await this.findDeviceByDeviceIdentifier(deviceIdentifier)
      if (!created) {
        throw new Error('Failed to create device')
      }
      return created
    }

    if ((name ?? null) !== existing.name || (hardwareInfo ? JSON.stringify(hardwareInfo) : null) !== (existing.hardwareInfo ? JSON.stringify(existing.hardwareInfo) : null)) {
      await this.db.sql(
        `update devices set name = ?, hardware_info = ?, updated_at = CURRENT_TIMESTAMP where device_id = ?`,
        [name ?? existing.name, stringifyJSON(hardwareInfo ?? existing.hardwareInfo), deviceIdentifier]
      )
    }

    return (await this.findDeviceByDeviceIdentifier(deviceIdentifier))!
  }

  async markDeviceOnline(deviceIdentifier: string): Promise<void> {
    await this.db.sql(
      `update devices set last_seen_at = ?, last_status = ?, updated_at = CURRENT_TIMESTAMP where device_id = ?`,
      [new Date(), stringifyJSON({ online: true }), deviceIdentifier]
    )
  }

  async markDeviceOffline(deviceIdentifier: string): Promise<void> {
    await this.db.sql(
      `update devices set last_status = ?, updated_at = CURRENT_TIMESTAMP where device_id = ?`,
      [stringifyJSON({ online: false }), deviceIdentifier]
    )
  }

  async listBoundDevicesForUser(userId: number): Promise<DeviceRecord[]> {
    const result = await this.db.sql(
      `select d.* from user_device_bindings b join devices d on b.device_id = d.id where b.user_id = ? and b.status = 'BOUND'`,
      [userId]
    ) as Record<string, any>[]
    return result.map((row) => normalizeDeviceRecord(toCamelCase<DeviceRecord>(row)!)).filter(Boolean) as DeviceRecord[]
  }

  async getBoundDeviceForUser(userId: number, deviceIdentifier: string): Promise<DeviceRecord | undefined> {
    const result = await this.db.sql(
      `select d.* from user_device_bindings b join devices d on b.device_id = d.id where b.user_id = ? and b.status = 'BOUND' and d.device_id = ? limit 1`,
      [userId, deviceIdentifier]
    ) as Record<string, any>[]
    const record = toCamelCase<DeviceRecord>(result[0])
    return record ? normalizeDeviceRecord(record) : undefined
  }

  async bindUserToDevice(userId: number, device: DeviceRecord): Promise<UserDeviceBindingRecord> {
    const existing = await this.db.sql(
      `select * from user_device_bindings where user_id = ? and device_id = ? limit 1`,
      [userId, device.id]
    ) as Record<string, any>[]
    const existingRecord = toCamelCase<UserDeviceBindingRecord>(existing[0])
    if (!existingRecord) {
      await this.db.sql(
        `insert into user_device_bindings (user_id, device_id, status, bound_at) values (?, ?, 'BOUND', ?)`,
        [userId, device.id, new Date()]
      )
    } else if (existingRecord.status !== 'BOUND') {
      await this.db.sql(
        `update user_device_bindings set status = 'BOUND', bound_at = ?, unbound_at = NULL, updated_at = CURRENT_TIMESTAMP where id = ?`,
        [new Date(), existingRecord.id]
      )
    }

    const refreshed = await this.db.sql(
      `select * from user_device_bindings where user_id = ? and device_id = ? limit 1`,
      [userId, device.id]
    ) as Record<string, any>[]
    const record = toCamelCase<UserDeviceBindingRecord>(refreshed[0])
    if (!record) {
      throw new Error('Failed to bind user to device')
    }
    return record
  }

  async upsertDeviceConfig(device: DeviceRecord, configs: DeviceConfigMessage[], updatedBy: DeviceConfigUpdatedBy): Promise<DeviceConfigRecord> {
    const existing = await this.db.sql(
      `select * from device_configs where device_id = ? limit 1`,
      [device.id]
    ) as Record<string, any>[]
    const payload = stringifyJSON(configs)

    if (!existing[0]) {
      await this.db.sql(
        `insert into device_configs (device_id, configs, updated_by) values (?, ?, ?)`,
        [device.id, payload, updatedBy]
      )
    } else {
      await this.db.sql(
        `update device_configs set configs = ?, updated_by = ?, updated_at = CURRENT_TIMESTAMP where device_id = ?`,
        [payload, updatedBy, device.id]
      )
    }

    const refreshed = await this.db.sql(
      `select * from device_configs where device_id = ? limit 1`,
      [device.id]
    ) as Record<string, any>[]
    const record = toCamelCase<DeviceConfigRecord>(refreshed[0])
    if (!record) {
      throw new Error('Failed to load device config')
    }
    return normalizeDeviceConfigRecord(record)
  }

  async getDeviceConfig(device: DeviceRecord): Promise<DeviceConfigRecord | undefined> {
    const result = await this.db.sql(
      `select * from device_configs where device_id = ? limit 1`,
      [device.id]
    ) as Record<string, any>[]
    const record = toCamelCase<DeviceConfigRecord>(result[0])
    return record ? normalizeDeviceConfigRecord(record) : undefined
  }

  async updateDeviceLastStatus(deviceIdentifier: string, status: Record<string, any> | null): Promise<void> {
    await this.db.sql(
      `update devices set last_status = ?, last_seen_at = ?, updated_at = CURRENT_TIMESTAMP where device_id = ?`,
      [stringifyJSON(status), status ? new Date() : null, deviceIdentifier]
    )
  }
}

export const defaultStorage: Storage = new MysqlStorage(defaultMysqlDatabase)

function toCamelCase<T>(row?: Record<string, any>): T | undefined {
  if (!row) {
    return undefined
  }
  const result: Record<string, any> = {}
  for (const key of Object.keys(row)) {
    result[key.replace(/_([a-z])/g, (_, letter: string) => letter.toUpperCase())] = row[key]
  }
  return result as T
}

function stringifyJSON(value: any): string | null {
  if (value == null) {
    return null
  }
  return JSON.stringify(value)
}

function normalizeDeviceRecord(record: DeviceRecord): DeviceRecord {
  return {
    ...record,
    hardwareInfo: parseJSON<Record<string, any>>(record.hardwareInfo),
    lastStatus: parseJSON<Record<string, any>>(record.lastStatus)
  }
}

function normalizeDeviceConfigRecord(record: DeviceConfigRecord): DeviceConfigRecord {
  return {
    ...record,
    configs: parseJSON<DeviceConfigMessage[]>(record.configs) ?? []
  }
}

function parseJSON<T>(value: any): T | null {
  if (value == null) {
    return null
  }
  if (typeof value === 'object') {
    return value as T
  }
  try {
    return JSON.parse(String(value)) as T
  } catch {
    return null
  }
}